CREATE DATABASE [RealtorPortal]

GO

USE [RealtorPortal]

GO
CREATE TABLE Administrators
(
	AdminId int identity not null primary key,
	Username varchar(50)not null,
	[Password] varchar(50) not null,
	Email varchar(100),
	Fullname varchar(50)
)

GO

CREATE TABLE Roles
(
	RoleId int identity primary key not null,
	RoleName varchar(50),
	RoleDescription varchar(MAX)
)

go

CREATE TABLE Users
(
	UserId int identity primary key not null,
	Username varchar(50) not null,
	[Password] varchar(100) not null,
	FirstName varchar(50),
	LastName varchar(50),
	Email varchar(100),
	Phone varchar(15),
	Company varchar(200),
	[Address] varchar(MAX),
	RegDate varchar(50),
	ExpDate varchar(50),
	HidedEmail bit default 0,
	PaypalAccount varchar(200),
	IsActive bit default 1,
	RoleId int foreign key references Roles(RoleId)
)
go

CREATE TABLE Packages
(
	PackageId int identity primary key not null,
	PackageName varchar(100) not null,
	Price money,
	NumOfAds int,
	NumOfImg int,
	Duration int,
	IsVIP bit,
	IsActive bit default 1
)

go

CREATE TABLE Subscription
(
	SubId int identity primary key not null,
	SubDate varchar(50),
	UserId int not null foreign key references Users(UserId),
	PackageId int not null foreign key references Packages(PackageId),
	IsPaid bit default 1
)

go

CREATE TABLE News
(
	NewsId int identity primary key not null,
	AdminId int not null foreign key references Administrators(AdminId),
	Title varchar(max),
	Content text,
	[NewsImage] varchar(250),
	WritedDate varchar(50)
)
go
CREATE TABLE Countries
(
	CountryId int identity primary key not null,
	CountryName varchar(100)
)

go
CREATE TABLE Regions
(
	RegionId int identity primary key not null,
	RegionName varchar(100),
	CountryId int not null foreign key references Countries(CountryId)
)

go

CREATE TABLE Cities
(
	CityId int identity primary key not null,
	CityName varchar(100),
	RegionId int not null foreign key references Regions(RegionId)
)
go

CREATE TABLE Districts
(
	DistrictId int identity primary key not null,
	DistrictName varchar(100),
	CityId int not null foreign key references Cities(CityId)
)

go

CREATE TABLE Categories
(
	CategoryId int identity primary key not null,
	CategoryName varchar(200),
	[Description] varchar(max),
	IsActive bit default 1
)

go

CREATE TABLE Advertisements
(
	AdvId int identity primary key not null,
	CategoryId int not null foreign key references Categories(CategoryId),
	UserId int not null foreign key references Users(UserId),
	DistrictId int not null foreign key references Districts(DistrictId),
	PackageId int not null foreign key references Packages(PackageId),
	Title varchar(250),
	[Type] varchar(50),
	ImageUrl varchar(MAX),
	Price money,
	PostedDate varchar(50),
	ExpDate varchar(50),
	LandArea float,
	[Floor] int,
	[BedRoom] int,
	[BathRoom] int,
	BuiltYear int,
	[Address] varchar(MAX),
	[Description] text,
	Amenities varchar(MAX),
	IsFeatured bit,
	IsActive bit default 1
)

go

CREATE TABLE AdsImages
(
	Id int identity primary key not null,
	AdvId int not null foreign key references Advertisements(AdvId),
	ImageUrl varchar(200)
)

go

CREATE TABLE AdsComment
(
	CommentId int identity primary key not null,
	Title varchar(100),
	Content varchar(MAX),
	[Date] varchar(50),
	FullName varchar(100),
	Email varchar(100),
	AdvId int not null foreign key references Advertisements(AdvId)
)

